<!doctype html public "-//w3c//dtd html 4.0 transitional//en">


<!-- WARNING! This file is generated. -->
<!-- To alter documentation, edit files in src directory -->


<html><head>
<title>Test a Procedure</title>
<link rel="stylesheet" href="utplsql.css" content="text/css">
<meta name="keywords" content="utPLSQL, PL\SQL, Unit Testing, Framework, Oracle"/>
<meta name="description" content="Unit Testing PL\SQL"/>
<meta name="title" content="Test a Procedure"/>
<meta name="author" content="Steven Feuerstein, Chris Rimmer, Patrick Barel"/>
<meta name="copyright" content="(C) 2000-2005 Steven Feuerstein, Chris Rimmer, Patrick Barel"/>
</head><body>
<div class="purple_bar"><a href="index.html"><img src="utplsql.jpg" border=0></a></div>
<p>[ <A href="index.html">Home</A>
 | <A href="started.html">Getting Started</A>
 | <A href="buildpack.html">Build Test Packages</A>
 | <A href="examples.html">Examples</A>
 | <A href="userguide.html">User Guide</A>
 | <A href="release.html">Release Notes</A>
 | <A href="map.html">Document Map</A> ]</p>
<p><A href="examples.html">&lt; Previous Section: Examples</A> | <A href="testfunc.html">Next Section: Test a Function &gt;</A></p>
<!-- Begin utPLSQL Body -->
<!-- $Id: testproc.html,v 1.3 2002/07/25 10:30:58 chrisrimmer Exp $ -->
<h1>
Test a Procedure</h1>

<p>There are a couple of scenarios to consider:
<ul>
<li>
The procedure runs some code and then passes back results through the parameter
list. In this case, I can write a unit test that analyzes the OUT and IN
OUT argument values.</li>

The procedure runs some code, which changes
other elements of the application (such as a database table or a file).
The parameter list does not contain arguments that can be analyzed for
successful execution. So to assert success, I will need to analyze/compare
the data structures that have been modified.
</ul>

<h3>
Test Success Through Parameters</h3>

We'll start with a really simple example. I
have built a procedure that accepts two dates and returns the number of
seconds between them. Here it is:

<pre>/*file calc_secs_between.sp */
CREATE OR REPLACE PROCEDURE calc_secs_between (
   date1 IN DATE,
   date2 IN DATE,
   secs OUT NUMBER)
IS
BEGIN
   -- 24 hours in a day, 
   -- 60 minutes in an hour,
   -- 60 seconds in a minute...
   secs := (date2 - date1) * 24 * 60 * 60;
END;
/</pre>

After compiling my code cleanly, I <a href="utgen.html">generate
my test package</a>:

<pre>SQL&gt; SET SERVEROUTPUT ON FORMAT WRAPPED
SQL&gt; exec utGen.testpkg ('calc_secs_between ')
CREATE OR REPLACE PACKAGE ut_calc_secs_between
IS
   PROCEDURE ut_setup;
   PROCEDURE ut_teardown;

   -- For each program to test...
   PROCEDURE ut_CALC_SECS_BETWEEN;
END ut_calc_secs_between;
/
CREATE OR REPLACE PACKAGE BODY ut_calc_secs_between
IS
   PROCEDURE ut_setup
   IS
   BEGIN
      NULL;
   END;

   PROCEDURE ut_teardown
   IS
   BEGIN
      NULL;
   END;

   -- For each program to test...
   PROCEDURE ut_CALC_SECS_BETWEEN IS
   BEGIN
      CALC_SECS_BETWEEN (
            DATE1 =&gt; ''
            ,
            DATE2 =&gt; ''
            ,
            SECS =&gt; ''
       );

      utAssert.this (
         'Test of CALC_SECS_BETWEEN',
         '&lt;boolean expression&gt;'
         );
   END ut_CALC_SECS_BETWEEN;

END ut_calc_secs_between;
/</pre>

I generated the output to the screen, but it
is actually easier to deposit the code directly into two separate files
for package spec and body, ut_calc_secs_between.pks and ut_calc_secs_between.pkb,
which I do as follows:

<pre>SQL&gt; exec utGen.testpkg ('calc_secs_between ', output_type_in =&gt; utGen.c_file)</pre>

By conforming to this standard, utPLSQL can
automatically compile this code before each test. I now edit the ut_calc_secs_between
procedure to test for various cases:

<pre><a name="useseq"></a>PROCEDURE ut_CALC_SECS_BETWEEN 
IS
   secs PLS_INTEGER;
BEGIN
   CALC_SECS_BETWEEN (
         DATE1 =&gt; SYSDATE
         ,
         DATE2 =&gt; SYSDATE
         ,
         SECS =&gt; secs
    );

   utAssert.eq (
      'Same dates',
      secs, 
      0
      );
      
   CALC_SECS_BETWEEN (
         DATE1 =&gt; SYSDATE
         ,
         DATE2 =&gt; SYSDATE+1
         ,
         SECS =&gt; secs
    );

   utAssert.eq (
      'Exactly one day',
      secs, 
      24 * 60 * 60
      );
      
END ut_CALC_SECS_BETWEEN;</pre>

and now I can run my test:

<pre>SQL&gt; exec utplsql.test ('calc_secs_between')
.
&gt;    SSSS   U     U   CCC     CCC   EEEEEEE   SSSS     SSSS
&gt;   S    S  U     U  C   C   C   C  E        S    S   S    S
&gt;  S        U     U C     C C     C E       S        S
&gt;   S       U     U C       C       E        S        S
&gt;    SSSS   U     U C       C       EEEE      SSSS     SSSS
&gt;        S  U     U C       C       E             S        S
&gt;         S U     U C     C C     C E              S        S
&gt;   S    S   U   U   C   C   C   C  E        S    S   S    S
&gt;    SSSS     UUU     CCC     CCC   EEEEEEE   SSSS     SSSS
.
 SUCCESS: "calc_secs_between"</pre>

Certainly, there are a variety of other conditions
to test, but this should give you a good idea of how to go about it!

<h3>
Test Success by Analyzing Impact</h3>

Now let's consider a more complicated situation.
I have a procedure that truncates all the rows in the specified table.
To do this I just use dynamic SQL, as you can see in:

<pre>/*file truncit.sp */
CREATE OR REPLACE PROCEDURE truncit (
   tab IN VARCHAR2,
   sch IN VARCHAR2 := NULL
)
IS
BEGIN
   EXECUTE IMMEDIATE 'truncate table ' || NVL (sch, USER) || '.' || tab;
END;
/</pre>

After I run this test, I cannot simply check
the value returned by the procedure. Instead, I must check to see how many
rows are left in the table. Fortunately, I have another dynamic SQL utility
to help me out here, one that returns the count of rows in any table:
(Note that you could also use <a href="utassert.html#eqqueryvalue">utAssert.eqqueryvalue</a> here.)

<pre>/*file tabcount.sf */
CREATE OR REPLACE FUNCTION tabcount (
   sch IN VARCHAR2,
   tab IN VARCHAR2)
   RETURN INTEGER
IS
   retval  INTEGER;
BEGIN
   EXECUTE IMMEDIATE 
      'SELECT COUNT(*) FROM ' || sch || '.' || tab
      INTO retval; 
   RETURN retval;
EXCEPTION
    WHEN OTHERS 
    THEN
       RETURN NULL; 
END;
/</pre>

So I will <a href="utgen.html">generate a package</a>
to test truncit and then modify the package body:

<pre>SQL&gt; SET SERVEROUTPUT ON FORMAT WRAPPED
SQL&gt; exec utGen.testpkg ('truncit', output_type_in =&gt; utGen.c_file)</pre>

To run my test, I need to truncate a table.
That is an irreversible action, so I will create a "temporary" table in
the setup procedure and drop it in the teardown procedure. Then I will
run my code and use tabCount to validate the results:

<pre>/*file ut_truncit.pkb */
CREATE OR REPLACE PACKAGE BODY ut_truncit
IS
   PROCEDURE ut_setup
   IS
   BEGIN
      EXECUTE IMMEDIATE 
         'CREATE TABLE temp_emp AS SELECT * FROM employee';
   END;
   
   PROCEDURE ut_teardown
   IS
   BEGIN
      EXECUTE IMMEDIATE 
         'DROP TABLE temp_emp';
   END;

   -- For each program to test...
   PROCEDURE ut_TRUNCIT IS
   BEGIN
      TRUNCIT (
            TAB =&gt; 'temp_emp'
            ,
            SCH =&gt; USER
       );

      utAssert.eq (
         'Test of TRUNCIT',
         tabcount (USER, 'temp_emp'),
         0
         );
   END ut_TRUNCIT;

END ut_truncit;
/</pre>

Not quite as straightforward as checking values
returned in OUT or IN OUT arguments, but not too awful, right? Of course,
things can get considerably more complicated as your code (and the results
you must test for) grows more complex. Regardless, you will find it easier
to build and run your tests through utPLSQL than through more ad hoc and
considerably less organized approaches.


<!-- End utPLSQL Body -->
<p><A href="examples.html">&lt; Previous Section: Examples</A> | <A href="testfunc.html">Next Section: Test a Function &gt;</A></p>
<div class="purple_bar"><a href="index.html"><img src="utplsql.jpg" border=0></a></div>
<p class="copyright">Copyright (C) 2000-2005 <A href="mailto:steven@stevenfeuerstein.com">Steven Feuerstein<A>, <A href="mailto:c@24.org.uk">Chris Rimmer<A>, <A href="mailto:pbarel@vda.nl">Patrick Barel<A> All rights reserved</p>
</body></html>